declare @TempTablasCount table (id int identity, nombre varchar(100),Cantidad Bigint )
declare @count int
declare @query nvarchar(max)
		,@tabla nvarchar(100)
		,@id int

declare qTabla Cursor for
		select tabla from (select quotename(s.name) + '.' + QUOTENAME(t.name) as 'tabla' from sys.tables as t
						join sys.schemas as s on t.schema_id = s.schema_id) as Aux

open qTabla
Fetch next from qTabla into @tabla
while @@FETCH_STATUS = 0

begin

set @query = N'select @cuenta=count(*) from ' + @tabla 
exec sp_executesql @query,N'@cuenta int output',@cuenta = @count output

insert into @TempTablasCount 
values (@tabla, @count)
fetch next from qTabla into @tabla

end
close qTabla
Deallocate qTabla 

select * from @TempTablasCount